﻿using DatabaseTransfer.Connections;
using DatabaseTransfer.Forms;
using DatabaseTransfer.Interfaces;
using DatabaseTransfer.Logs;
using DatabaseTransfer.Tables;
using log4net;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DatabaseTransfer.Databases
{
    public class MySql : AbstractDatabase
    {
        private static ILog logger = LogFactory.GetLogger(typeof(MySql));

        private string connectionString;

        public MySql(DatabaseConnector connector)
            : base(connector)
        {
            this.connectionString = String.Format("Server = {0}; Port = {1}; Database = {2}; Uid = {3}; Pwd = {4};",
                            this.Connector.URL, this.Connector.Port, this.Connector.Database, this.Connector.User, this.Connector.Password);
        }

        public override bool Connect()
        {
            return true;
        }

        public override bool Disconnect()
        {
            return true;
        }

        public override List<Table> GetAvailableTables()
        {
            logger.Debug("Getting Available Tables");
            List<Table> tables = new List<Table>();
            string qry = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = @database";

            using (MySqlConnection connection = new MySqlConnection(this.connectionString))
            using (MySqlCommand command = connection.CreateCommand())
            {
                try
                {
                    connection.Open();
                    command.CommandText = qry;
                    command.Parameters.AddWithValue("@database", this.Connector.Database);
                    command.Prepare();
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Table table = GetTableInfo(reader);
                            tables.Add(table);
                        }
                    }
                }
                catch (Exception ex)
                {
                    logger.Fatal("Unable to get table columns", ex);
                    return null;
                }
            }

            int tableCount = tables == null ? 0 : tables.Count;

            logger.Debug(String.Format("Found {0} tables", tableCount));

            return tables;
        }

        private Table GetTableInfo(MySqlDataReader reader)
        {
            Table table = new Table();
           
            foreach (PropertyInfo fieldInfo in table.GetType().GetProperties())
            {
                try
                {
                    ColumnInfoAttribute columnAttr = (ColumnInfoAttribute)fieldInfo.GetCustomAttribute(typeof(ColumnInfoAttribute));

                    if (reader[columnAttr.ColumnName] == DBNull.Value) continue;

                    if (columnAttr.Type == typeof(String))
                    {
                        fieldInfo.SetValue(table, reader.GetString(columnAttr.ColumnName));
                    }
                    else if (columnAttr.Type == typeof(int))
                    {
                        fieldInfo.SetValue(table, reader.GetInt32(columnAttr.ColumnName));
                    }
                    else if (columnAttr.Type == typeof(DateTime))
                    {
                        fieldInfo.SetValue(table, reader.GetDateTime(columnAttr.ColumnName));
                    }
                }
                catch (Exception)
                {
                    continue;
                }

            }
            return table;
        }

        public override List<TableColumn> GetTableColumns(string tableName)
        {
            List<TableColumn> columns = new List<TableColumn>();

            logger.Debug("Getting Columns from table " + tableName);
            try
            {
                
                using (MySqlConnection connection = new MySqlConnection(this.connectionString))
                using (MySqlCommand command = connection.CreateCommand())
                {
                    connection.Open();
                    command.CommandText = "select * from " + tableName;
                    string cmd = command.CommandText;
                    using (MySqlDataAdapter adaptor = new MySqlDataAdapter(command))
                    {
                        DataSet set = new DataSet();
                         adaptor.Fill(set, tableName);

                        DataTable table = set.Tables[tableName];

                        foreach (DataColumn column in table.Columns)
                        {
                            TableColumn tblColumn = new TableColumn()
                            {
                                ColumnName = column.ColumnName,
                                ColumnType = column.DataType
                            };

                            columns.Add(tblColumn);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Fatal("Unable to get table columns for table " + tableName, ex);
            }

            return columns;
        }

        public override TableResults GetData(string tableName, List<TableColumn> columns)
        {
            StringBuilder sb = new StringBuilder("select ");

            for (int i = 0; i < columns.Count; i++)
            {
                TableColumn column = columns[i];
                sb.Append(String.Format("{0}{1} ", column.ColumnName,
                    i < (columns.Count - 1) ? "," : ""));
            }

            sb.Append(String.Format(" from {0}", tableName));

            TableResults results = new TableResults();

            using (MySqlConnection connection = new MySqlConnection(this.connectionString))
            using (MySqlCommand command = connection.CreateCommand())
            {
                try
                {
                    connection.Open();
                    command.CommandText = sb.ToString();
                    command.Prepare();
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            TableRow row = new TableRow();
                            foreach (TableColumn column in columns)
                            {
                                TableColumn newColumn = new TableColumn();
                                newColumn.ColumnName = column.ColumnName;
                                newColumn.ColumnType = column.ColumnType;
                                newColumn.ColumnConversion = column.ColumnConversion;
                                newColumn.ColumnValue = reader[column.ColumnName];
                                row.AddColumn(newColumn);
                            }

                            if (row.ColumnCount > 0)
                            {
                                results.AddTableRow(row);
                            }

                        }
                    }
                }
                catch (Exception ex)
                {
                    logger.Fatal("Unable to get table columns", ex);
                    return null;
                }
            }

            return results;
        }

        public override bool WriteData(string tableName, List<TableColumn> columns, TableResults results)
        {
            StringBuilder sb = new StringBuilder(String.Format("insert into {0} (", tableName));

            for (int index = 0; index < columns.Count; index++)
            {
                TableColumn column = columns[index];
                sb.Append(String.Format("{0}{1} ", column.ColumnName,
                    index < (columns.Count - 1) ? "," : ""));
            }

            sb.Append(") values (");

            for (int index = 1; index <= results.RowCount; index++)
            {
                TableRow row = results.GetTableRow(index);

                for (int colIndex = 0; colIndex < row.ColumnCount; colIndex++)
                {
                    TableColumn column = row.GetColumn(colIndex);

                    string value = column.ColumnConversion == null ? column.ColumnValue.ToString() :
                        column.ColumnConversion.Replace("{value}", column.ColumnValue.ToString());

                    sb.Append(String.Format("'{0}'{1} ", value,
                        colIndex < (row.ColumnCount - 1) ? "," : ""));
                }

                sb.Append((index < (results.RowCount - 1) ? "),(" : ""));
            }

            sb.Append(")");

            string insertQuery = sb.ToString();

            using (MySqlConnection connection = new MySqlConnection(this.connectionString))
            using (MySqlCommand command = connection.CreateCommand())
            {
                try
                {
                    connection.Open();
                    command.CommandText = insertQuery;
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        string value = reader.ToString();
                    }
                }
                catch (Exception ex)
                {
                    logger.Error("Unable to execute insert", ex);
                    return false;
                }
            }

            return true;
        }
    }
}
